IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MergeAdmSponsors]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[MergeAdmSponsors]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

Create procedure [dbo].[MergeAdmSponsors]
(
	@fromId int,
	@toId int
)
AS

BEGIN

  declare @myRowCount int -- Local @@ROWCOUNT


  if @fromId = @toId
  begin
    select 'Id values cannot be the same (' + convert(varchar,@fromId) + ')'
    return
  end

  select @myRowCount=count(*) from AdmSponsor
  where Id = @toId

  if 0 = @myRowCount
  begin
    select '"TO" Id is invalid (' + convert(varchar,@toId) + ')'
    return
  end 


  begin transaction


  BEGIN TRY

  Declare @MapTable  as table(mapId int)

  insert into PreMergeAdmSponsor
  ( Id, Name, ShortName, Description, AdmSponsorTypeId, DateCreated,
	RexSponsorId, InfoEdSponCode, PSCustCode )
  select Id, Name, ShortName, Description, AdmSponsorTypeId, getdate(),
	RexSponsorId, InfoEdSponCode, PSCustCode
  from AdmSponsor
  where Id = @fromId

  select @myRowCount = @@ROWCOUNT

  if 0 = @myRowCount
  begin
    rollback transaction
    select '"FROM" Id is invalid (' + convert(varchar,@fromId) + ')'
    return
  end 


  -- AdmSponsor_COIOrganization_Map table
  insert into AdmSponsorMergeHistory
  ( NewId, OldId, TableName, IdValue, Comments )
  select @toId, @fromId, 'AdmSponsor_COIOrganization_Map', COIOrganizationId, 'COIOrganizationId'
  from AdmSponsor_COIOrganization_Map
  where AdmSponsorId = @fromId

  select @myRowCount = @@ROWCOUNT

  if @myRowCount > 0
  begin

    insert into @MapTable (mapId)
    select COIOrganizationId
    from AdmSponsor_COIOrganization_Map
    where AdmSponsorId in (@fromId, @toId)

    delete AdmSponsor_COIOrganization_Map
    where AdmSponsorId in (@fromId, @toId)

    insert into AdmSponsor_COIOrganization_Map
    ( AdmSponsorId, COIOrganizationId )
    select distinct @toId, mapId from @MapTable
  end


  -- AgrSubContractor table
  insert into AdmSponsorMergeHistory
  ( NewId, OldId, TableName, IdValue )
  select @toId, @fromId, 'AgrSubContractor', Id
  from AgrSubContractor
  where AdmSponsorId = @fromId

  select @myRowCount = @@ROWCOUNT

  if @myRowCount > 0
  begin
    update AgrSubContractor
    set AdmSponsorId = @toId
    where AdmSponsorId = @fromId
  end


  -- ClinicalTrialBudget table
  insert into AdmSponsorMergeHistory
  ( NewId, OldId, TableName, IdValue )
  select @toId, @fromId, 'ClinicalTrialBudget', Id
  from ClinicalTrialBudget
  where SponsorId = @fromId

  select @myRowCount = @@ROWCOUNT

  if @myRowCount > 0
  begin
    update ClinicalTrialBudget
    set SponsorId = @toId
    where SponsorId = @fromId
  end


  -- Protocol table
  insert into AdmSponsorMergeHistory
  ( NewId, OldId, TableName, IdValue )
  select @toId, @fromId, 'Protocol', Id
  from Protocol
  where AdmSponsorId = @fromId

  select @myRowCount = @@ROWCOUNT

  if @myRowCount > 0
  begin
    update Protocol
    set AdmSponsorId = @toId
    where AdmSponsorId = @fromId
  end


  -- ProtocolAdmSponsor_Map table
  insert into AdmSponsorMergeHistory
  ( NewId, OldId, TableName, IdValue, Comments )
  select @toId, @fromId, 'ProtocolAdmSponsor_Map', ProtocolId, 'ProtocolId'
  from ProtocolAdmSponsor_Map
  where AdmSponsorId = @fromId

  select @myRowCount = @@ROWCOUNT

  if @myRowCount > 0
  begin
    insert into @MapTable (mapId)
    select ProtocolId
    from ProtocolAdmSponsor_Map
    where AdmSponsorId in (@fromId, @toId)

    delete ProtocolAdmSponsor_Map
    where AdmSponsorId in (@fromId, @toId)

    insert into ProtocolAdmSponsor_Map
    ( AdmSponsorId, ProtocolId )
    select distinct @toId, mapId from @MapTable
  end


  -- QuestionnaireAnswer table
  insert into AdmSponsorMergeHistory
  ( NewId, OldId, TableName, IdValue )
  select @toId, @fromId, 'QuestionnaireAnswer', qa.Id
  from QuestionnaireAnswer qa
    inner join QuestionnaireVersion qv on qa.QuestionnaireVersionId = qv.Id 
    inner join Questionnaire q on qv.QuestionnaireId = q.Id 
  where
    q.Name like 'Sponsor%' and
    qa.ExternalId = @fromId

  select @myRowCount = @@ROWCOUNT

  if @myRowCount > 0
  begin
    update QuestionnaireAnswer
    set ExternalId = @toId
    from QuestionnaireAnswer qa
      inner join QuestionnaireVersion qv on qa.QuestionnaireVersionId = qv.Id 
      inner join Questionnaire q on qv.QuestionnaireId = q.Id 
    where
      q.Name like 'Sponsor%' and
      qa.ExternalId = @fromId
  end


  -- SvcEntity_AdmSponsor_Map table
  insert into AdmSponsorMergeHistory
  ( NewId, OldId, TableName, IdValue )
  select @toId, @fromId, 'SvcEntity_AdmSponsor_Map', Id
  from SvcEntity_AdmSponsor_Map
  where AdmSponsorId = @fromId

  select @myRowCount = @@ROWCOUNT

  if @myRowCount > 0
  begin
    update SvcEntity_AdmSponsor_Map
    set AdmSponsorId = @toId
    where AdmSponsorId = @fromId
  end



  -- AgrAgreement_Fct and AgrBudget_Fct table
  update AgrAgreement_Fct
  set SponsorId = @toId
  where SponsorId = @fromId

  update AgrBudget_Fct
  set SponsorId = @toId
  where SponsorId = @fromId

  delete AdmSponsor where Id = @fromId

  commit transaction

  END TRY
  BEGIN CATCH

    DECLARE @ErrorMessage NVARCHAR(4000)
    DECLARE @ErrorSeverity INT
    DECLARE @ErrorState INT

    SELECT @ErrorMessage = ERROR_MESSAGE(),
           @ErrorSeverity = ERROR_SEVERITY(),
           @ErrorState = ERROR_STATE();

    rollback transaction

    -- Use RAISERROR inside the CATCH block to return 
    -- error information about the original error that 
    -- caused execution to jump to the CATCH block.
    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State
               )
  END CATCH

END
GO
